package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.TeacherContinueInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.ContinueInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 教师续报信息 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2023-06-18
 */
public interface TeacherContinueInfoMapper extends BaseMapper<TeacherContinueInfo> {
    @Select({"<script>",
            "SELECT",
            "*,",
            "count(id) as student_all_count,",
//                      "getStaffNameForId(teacher_id) as teacher_name,",
            "getSchoolName(school_id) as school_name,",
            "SUM( CASE WHEN is_presort in(1,2,3) THEN 1 ELSE 0 END ) AS scheduling_count,",
            "SUM( CASE WHEN is_presort in(1,2,3) and presort_date BETWEEN #{startDate} AND #{endDate}  THEN 1 ELSE 0 END ) AS date_scheduling_count",
            "FROM",
            "(",
            "SELECT",
            "t01.id,",
            "t01.school_id,",
            "t03.year_class_id,",
            "t03.course_type_id,",
            "t01.student_id,",
            "t01.grade_id,",
            "t03.teacher_id,",
            "t03.`name` AS grade_name,",
            "t01.subjects_id,",
            "t01.lesson_type,",
            "t02.is_presort,",
            "DATE_FORMAT(t02.created_at, '%Y-%m-%d') as presort_date,",
            "sff.`name` AS teacher_name",
            "FROM",
            "(",
            "SELECT",
            "* ",
            "FROM",
            "student_grade_view ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND deleted = 0 ",
            "AND STATUS = 1 ",
            "AND is_last = 1 ",
            "and is_makeup = 0",
            "and (reading_status in(1,4,5,6) or (reading_status=3 and use_course_count&gt;0))",
            "and student_id not in(select student_id from renew_no_student where deleted=0 and year_part =${yearPart} and quarter_num = ${quarterNum})",
            "<when test='yearClassId==null or yearClassId==&apos;&apos;'>",
            "<when test='quarterNum==2'>",
            "and year_class_id &lt; 13",
            "</when>",
            "<when test='quarterNum!=2'>",
            "and year_class_id &lt; 13",
            "</when>",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "and year_class_id IN (${yearClassId})",
            "</when>",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "teacher_id,",
            "subjects_id ",
            ") AS t01",
            "LEFT JOIN (",
            "SELECT",
            "* ",
            "FROM",
            "student_grade ",
            "WHERE",
            "year_part = ${nextYearPart} ",
            "AND quarter_num = ${nextQuarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND deleted = 0 ",
            "AND is_presort in(1,2,3) ",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN grade AS t03 ON t01.grade_id = t03.id ",
            "INNER JOIN staff AS sff ON sff.id = t03.teacher_id",
            ") AS t",
            "where 1=1",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type != 4'>",
            "and teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id = ${trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type == 4'>",
            "and school_id in(select sourse_id from track_detail where deleted = 0 and track_id = ${trackId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id IN (${subjectsId})",
            "</when>",
            "<if test= 'type == 3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id in(${schoolId})",
            "</when>",
            "<when test='yearClassId!=null'>",
            "and year_class_id IN (${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null'>",
            "and course_type_id IN (${classTypeId})",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and teacher_name LIKE '%${name}%'",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id",
            "</if>",
            "</script>"})
    public abstract List<ContinueInfo> getRenewRecord(@Param("schoolId") String schoolId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("nextYearPart") Integer nextYearPart, @Param("nextQuarterNum") Integer nextQuarterNum, @Param("subjectsId") String subjectsId, @Param("type") Integer type, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonType") Integer lessonType, @Param("trackId") String trackId, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("name") String name);

}
